<?php
/*
 * $Id: MappedDaoTest.class.php 59 2009-05-23 07:19:09Z namba@craid.co.jp $
 * =============================================================================
 * Barge - web application framework on PHP5 
 * 
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 * 
 * For more information, see http://barge.googlecode.com/
 */
namespace barge\db;

use \UnitTestCase;

use barge\System;
use barge\db\Criteria;
use barge\db\MappedDao;
use barge\db\Order;
use barge\db\PDOConnection;
use barge\db\SQLException;
use barge\logging\LogFactory;
use barge\utils\ArrayList;
use barge\utils\Bean;
use barge\utils\HashMap;
use barge\utils\IndexOutOfBoundsException;
use barge\utils\IllegalAccessException;


// シンプルなDAO マッピング 
class UserDao extends MappedDao 
{
	protected function getMapping()
	{
		return array(
			'table' => 'TEST_USER',
			'model' => 'barge\db\User',
			'columns' => array(
				array(
					'type' => 'id',
					'column' => 'USER_ID',
					'property' => 'userId',
					'generator' => array(
					 	'type' => 'auto-increment',
					 ),
				),
				array(
					'type' => 'property',
					'column' => 'GENDER',
					'property' => 'gender', 
				),
				array(
					'type' => 'property',
					'column' => 'USER_NAME',
					'property' => 'userName',
				),
				array(
					'type' => 'property',
					'column' => 'SECT_ID',
					'property' => 'sectId',
					'phpType' => 'int',
				),
			)
		);
	}
}

// 複合キー	
class CorporatedUserDao extends MappedDao 
{
	protected function getMapping()
	{
		return array(
			'table' => 'TEST_CORP_USER',
			'model' => 'barge\db\CorporatedUser',
			'columns' => array(
				array(
					'type' => 'id',
					'column' => 'CORP_ID',
					'property' => 'corpId',
				),
				array(
					'type' => 'id',
					'column' => 'USER_ID',
					'property' => 'userId',
				),
				array(
					'type' => 'property',
					'column' => 'USER_NAME',
					'property' => 'userName',
				),
			)
			);
	}
}

class User extends Bean 
{
	protected $userId;
	protected $sectId;
	protected $userName;
	protected $gender;

	public function getGender()
	{
		return $this->gender;
	}

	public function setGender($value)
	{
		$this->gender = $value;
	}
	
	public function getUserId()
	{
		return $this->userId;
	}

	public function setUserId($value)
	{
		$this->userId = $value;
	}

	public function getUserName()
	{
		return $this->userName;
	}

	public function setUserName($value)
	{
		$this->userName = $value;
	}
	
	public function getSectId()
	{
		return $this->sectId;
	}

	public function setSectId($value)
	{
		$this->sectId = $value;
	}
}

class CorporatedUser extends Bean 
{
	protected $corpId;
	protected $userId;
	protected $userName;
	
	public function getCorpId()
	{
		return $this->corpId;
	}

	public function setCorpId($value)
	{
		$this->corpId = $value;
	}

	public function getUserId()
	{
		return $this->userId;
	}

	public function setUserId($value)
	{
		$this->userId = $value;
	}
	
	public function getUserName()
	{
		return $this->userName;
	}

	public function setUserName($value)
	{
		$this->userName = $value;
	}
}

/**
 * @see UnitTestCase
 */
class MappedDaoTest extends UnitTestCase 
{
	/**
	 * @var MappedDao
	 */
	private $userDao;
	
	/**
	 * @var MappedDao
	 */
	private $corporatedUserDao;
	
	public function setUp()
	{
		$conn = new PDOConnection('mysql:host=127.0.0.1;dbname=test', 'test', 'test');
		$conn->beginTransaction();
		try {
			$conn->executeUpdate("DROP TABLE TEST_USER");
		} catch (Exception $e) {}
		$conn->executeUpdate("CREATE TABLE TEST_USER ( USER_ID SERIAL, USER_NAME VARCHAR(256), GENDER VARCHAR(10), SECT_ID INT )");
		$conn->executeUpdate("INSERT INTO TEST_USER VALUES (1, '斉藤', 'm',  2)");
		$conn->executeUpdate("INSERT INTO TEST_USER VALUES (2, '近藤', null, 1)");
		$conn->executeUpdate("INSERT INTO TEST_USER VALUES (3, '井上', 'm',  1)");
		$conn->executeUpdate("INSERT INTO TEST_USER VALUES (4, '鈴木', 'f',  3)");

		$this->userDao = new UserDao($conn);

		try {
			$conn->executeUpdate("DROP TABLE TEST_CORP_USER");
		} catch (Exception $e) {}
		$conn->executeUpdate("CREATE TABLE TEST_CORP_USER ( CORP_ID INT, USER_ID INT, USER_NAME VARCHAR(256), PRIMARY KEY (CORP_ID, USER_ID))");
		$conn->executeUpdate("INSERT INTO TEST_CORP_USER VALUES (1, 1, '斉藤')");
		$conn->executeUpdate("INSERT INTO TEST_CORP_USER VALUES (1, 2, '近藤')");
		$conn->executeUpdate("INSERT INTO TEST_CORP_USER VALUES (2, 1, '井上')");
		$conn->executeUpdate("INSERT INTO TEST_CORP_USER VALUES (2, 2, '鈴木')");
		$conn->executeUpdate("INSERT INTO TEST_CORP_USER VALUES (2, 3, '横山')");
		
		$this->corporatedUserDao = new CorporatedUserDao($conn);
	}
	
	/**
	 * @test
	 */
	public function testGet() 
	{
		$result = $this->userDao->get(1);
		$this->assertTrue($result instanceof User);
		$this->assertTrue($result->getUserId() === '1');
		$this->assertTrue($result->getUserName() === '斉藤');
		$this->assertTrue($result->getGender() === 'm');
		
		// 複合キー 
		$result = $this->corporatedUserDao->get(2, 2);
		$this->assertTrue($result instanceof CorporatedUser);
		$this->assertTrue($result->getCorpId() === '2');
		$this->assertTrue($result->getUserId() === '2');
		$this->assertTrue($result->getUserName() === '鈴木');
		
		// 指定されたパラメータがPKの数と異なる 
		try {
			$this->corporatedUserDao->get(1);
			$this->fail();
		} catch (SQLException $e) {
			LogFactory::getLogger()->debug($e->getMessage());
		}
		
		try {
			$this->corporatedUserDao->get(1, 2, 2);
			$this->fail();
		} catch (SQLException $e) {
			LogFactory::getLogger()->debug($e->getMessage());
		}
	}

	/**
	 * @test
	 */
	public function testDeleteBy() 
	{
		// TEST : #addEquals
		$criteria = new Criteria();
		$criteria->addEquals('userId', 1);
		$this->userDao->deleteBy($criteria);
		
		$results = $this->userDao->findBy();
		$this->assertTrue($results->size() === 3);
		
		$this->userDao->deleteBy();
		
		$results = $this->userDao->findBy();
		$this->assertTrue($results->size() === 0);
	}
		
	/**
	 * @test
	 */
	public function testFindBy() 
	{
		// TEST : #addEquals
		$criteria = new Criteria();
		$criteria->addEquals('userId', 1);
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(0)->getUserName() === '斉藤');
		$this->assertTrue($results->get(0)->getGender() === 'm');
		$this->assertTrue($results->get(0)->getSectId() === 2);	// TEST : phpType=>'int'

		$criteria = new Criteria();
		$criteria->addEquals('gender', 'm');
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->size() === 2);
		
		$criteria = new Criteria();
		$criteria->addEquals('gender', null);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(0)->getGender() === null);
		
		// Empty criteria
		$criteria = new Criteria();
		$order = new Order();
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 4);
		
		// Order 
		$criteria = new Criteria();
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '2');
		$this->assertTrue($results->get(2)->getUserId() === '3');
		$this->assertTrue($results->get(3)->getUserId() === '4');
		
		// Order (desc)
		$criteria = new Criteria();
		$order = new Order();
		$order->add('userId', true);
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '4');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '2');
		$this->assertTrue($results->get(3)->getUserId() === '1');
		
		// Order (inline)
		$criteria = new Criteria();
		$results = $this->userDao->findBy($criteria, new Order('userId'));
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '2');
		$this->assertTrue($results->get(2)->getUserId() === '3');
		$this->assertTrue($results->get(3)->getUserId() === '4');
		
		// Find all
		$results = $this->userDao->findBy();
		$this->assertTrue($results->size() === 4);
		
		// #addNotEquals
		$criteria = new Criteria();
		$criteria->addNotEquals('userId', 1);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 3);
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(0)->getUserName() === '近藤');
		$this->assertTrue($results->get(0)->getGender() === null);
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '4');
		
		$criteria = new Criteria();
		$criteria->addNotEquals('gender', null);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 3);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '4');
				
		// #addBetween
		$criteria = new Criteria();
		$criteria->addBetween('userId', 2, 3);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(0)->getUserName() === '近藤');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		
		// #addExpression
		$criteria = new Criteria();
		$criteria->addExpression('#userId# = ?', new ArrayList(3));
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '3');
		$this->assertTrue($results->get(0)->getUserName() === '井上');
		$this->assertTrue($results->get(0)->getGender() === 'm');
		
		// >= #addGreaterEquals
		$criteria = new Criteria();
		$criteria->addGreaterEquals('userId', 3);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '3');
		$this->assertTrue($results->get(0)->getUserName() === '井上');
		$this->assertTrue($results->get(0)->getGender() === 'm');
		$this->assertTrue($results->get(1)->getUserId() === '4');
		
		// > #addGreaterEquals
		$criteria = new Criteria();
		$criteria->addGreaterThan('userId', 3);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '4');
		$this->assertTrue($results->get(0)->getUserName() === '鈴木');
		$this->assertTrue($results->get(0)->getGender() === 'f');
		
		// > #addGreaterEquals
		$criteria = new Criteria();
		$criteria->addIn('userId', new ArrayList(2, 4));
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(0)->getUserName() === '近藤');
		$this->assertTrue($results->get(0)->getGender() === null);
		$this->assertTrue($results->get(1)->getUserId() === '4');
		$this->assertTrue($results->get(1)->getUserName() === '鈴木');
		$this->assertTrue($results->get(1)->getGender() === 'f');
		
		// TODO : IN クエリーで NULL を指定する 
//		$criteria = new Criteria();
//		$criteria->addIn('gender', new ArrayList('m', null));
//		$order = new Order();
//		$order->add('userId');
//		$results = $this->userDao->findBy($criteria, $order);
//		$this->assertTrue($results->size() === 3);
//		$this->assertTrue($results->get(0)->getUserId() === '1');
//		$this->assertTrue($results->get(1)->getUserId() === '2');
//		$this->assertTrue($results->get(2)->getUserId() === '3');
		
		// <= #addGreaterEquals
		$criteria = new Criteria();
		$criteria->addLessEquals('userId', 2);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '2');

		
		// <= #addLessThan
		$criteria = new Criteria();
		$criteria->addLessThan('userId', 2);
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		
		// <= #addLessThan
		$criteria = new Criteria();
		$criteria->addLike('userName', '斉%');
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		
		// != #addNotEquals
		$criteria = new Criteria();
		$criteria->addNotEquals('userId', 2);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 3);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '4');
		
		// NOT IN #addNotIn
		$criteria = new Criteria();
		$criteria->addNotIn('userId', new ArrayList(1, 2, 3));
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '4');

		// シンプルなAND 
		$criteria = new Criteria();
		$criteria->addEquals('gender', 'm')->addLike('userName', '%藤');
		$results = $this->userDao->findBy($criteria);
		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		
		// シンプルなOR	
		$criteria = new Criteria();
		$criteria->addEquals('gender', null)->addEquals('userId', 4, Criteria::OP_OR);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria, $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(1)->getUserId() === '4');
		
		
		// (A AND B) OR (C AND D)
		$criteria1 = new Criteria();
		$criteria1->addEquals('gender', null)->addEquals('sectId', 1);
		$criteria2 = new Criteria();
		$criteria2->addEquals('gender', 'm')->addGreaterThan('sectId', 1);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria1->concat($criteria2, Criteria::OP_OR), $order);
		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '2');
		
		// (A OR B) AND (C AND D)
		$criteria1 = new Criteria();
		$criteria1->addEquals('gender', 'm')->addEquals('gender', 'f', Criteria::OP_OR);
		$criteria2 = new Criteria();
		$criteria2->addEquals('sectId', 1)->addEquals('sectId', 3, Criteria::OP_OR);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria1->concat($criteria2, Criteria::OP_AND), $order);

		$this->assertTrue($results->size() === 2);
		$this->assertTrue($results->get(0)->getUserId() === '3');
		$this->assertTrue($results->get(1)->getUserId() === '4');

		// (A OR B) AND (C AND D) AND E
		$criteria1 = new Criteria();
		$criteria1->addEquals('gender', 'm')->addEquals('gender', 'f', Criteria::OP_OR);
		$criteria2 = new Criteria();
		$criteria2->addEquals('sectId', 1)->addEquals('sectId', 3, Criteria::OP_OR);
		$criteria1->concat($criteria2, Criteria::OP_AND)->addLike('userName', '%木');
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria1, $order);

		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '4');
		
		// (A OR B) AND (C AND D) AND E
		$criteria1 = new Criteria();
		$criteria1->addEquals('gender', 'm')->addEquals('gender', 'f', Criteria::OP_OR);
		$criteria2 = new Criteria();
		$criteria2->addEquals('sectId', 1)->addEquals('sectId', 3, Criteria::OP_OR);
		$criteria3 = new Criteria();
		$criteria3->addLike('userName', '%木');
		$criteria1->concat($criteria2, Criteria::OP_AND)->concat($criteria3, Criteria::OP_AND);
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy($criteria1, $order);

		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '4');
		
		// ((A))
		$criteria1 = new Criteria();
		$criteria2 = new Criteria();
		$criteria2->addEquals('userId', 1);
		$criteria1->concat($criteria2);
		$results = $this->userDao->findBy($criteria1, $order);

		$this->assertTrue($results->size() === 1);
		$this->assertTrue($results->get(0)->getUserId() === '1');

		// ソート 
		$criteria = new Criteria();
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy(null, $order);
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '1');
		$this->assertTrue($results->get(1)->getUserId() === '2');
		$this->assertTrue($results->get(2)->getUserId() === '3');
		$this->assertTrue($results->get(3)->getUserId() === '4');
		
		$order = new Order();
		$order->add('userId', true);
		$results = $this->userDao->findBy(null, $order);
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '4');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '2');
		$this->assertTrue($results->get(3)->getUserId() === '1');
		
		// ソート（複数） 
		$criteria = new Criteria();
		$order = new Order();
		$order->add('sectId');
		$order->add('userId', true);
		$results = $this->userDao->findBy(null, $order);
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getUserId() === '3');
		$this->assertTrue($results->get(1)->getUserId() === '2');
		$this->assertTrue($results->get(2)->getUserId() === '1');
		$this->assertTrue($results->get(3)->getUserId() === '4');

		
		// TODO : ネストしたソート


	}

	/**
	 * @test
	 */
	public function testDelete() 
	{
		// シンプルケース 
		$order = new Order();
		$order->add('userId');
		$results = $this->userDao->findBy(null, $order);
		$this->assertTrue($results->size() === 4);
		$ret = $this->userDao->delete($results->get(0));
		$this->assertTrue($ret === 1);
		$results = $this->userDao->findBy();
		$this->assertTrue($results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(1)->getUserId() === '3');
		$this->assertTrue($results->get(2)->getUserId() === '4');
		
		// 複合キー
		$order = new Order();
		$order->add('corpId');
		$order->add('userId');
		$results = $this->corporatedUserDao->findBy(null, $order);
		$this->assertTrue($results->size() === 5);
		$ret = $this->corporatedUserDao->delete($results->get(0));
		$this->assertTrue($ret === 1);
		$results = $this->corporatedUserDao->findBy();
		$this->assertTrue($results->size() === 4);
		$this->assertTrue($results->get(0)->getCorpId() === '1' && $results->get(0)->getUserId() === '2');
		$this->assertTrue($results->get(1)->getCorpId() === '2' && $results->get(1)->getUserId() === '1');
		$this->assertTrue($results->get(2)->getCorpId() === '2' && $results->get(2)->getUserId() === '2');
		$this->assertTrue($results->get(3)->getCorpId() === '2' && $results->get(3)->getUserId() === '3');

		
		// TODO : キーが日付 
	}
	
	/**
	 * @test
	 */
	public function testUpdate() 
	{ 
		$result = $this->userDao->get(1);
		$this->assertTrue($result->getGender() === 'm');
		
		$result->setGender('f');
		$ret = $this->userDao->update($result);
		$this->assertTrue($ret === 1);
		$result = $this->userDao->get(1);
		$this->assertTrue($result->getGender() === 'f');
		
		// TODO : キーが日付
		// TODO : 日付項目の更新 

	}
	
	/**
	 * @test
	 */
	public function testRegist() 
	{ 
		$results = $this->userDao->findBy();
		$this->assertTrue($results->size() === 4);
		
		$domain = new User();
		$domain->setUserName('New User');
		$domain->setSectId('5');
		$domain->setGender('m');
		$this->userDao->regist($domain);
		
		$results = $this->userDao->findBy();
		$this->assertTrue($results->size() === 5);
		$this->assertTrue($domain->getUserId() !== null);
	}
	


}



?>